import pandas as pd
from comm_tools.config import Config
from comm_tools import date_tool
from datetime import date, timedelta, datetime
from comm_tools.database_mysql import open_mysql, load_to_MySQL_on_Cloud

def convert_date(value):
    if isinstance(value, (int, float)):
        return pd.to_datetime('1899-12-30')+pd.to_timedelta(value, unit='D')
    elif isinstance(value, str):
        date_str = value.replace('年', '-').replace('月', '')
        return datetime.strptime(date_str, "%Y-%m")
    else:
        return value

def get_data(file_path, sheet_name):
    df = pd.read_excel(file_path, sheet_name=sheet_name)

    # 修复日期
    df['并购重组年月'] = df['并购重组年月'].apply(convert_date)
    # transform datetype from datetime to date
    df['并购重组年月'] = df['并购重组年月'].dt.date

    # drop unused columns
    df.drop(columns=df.filter(regex='Unnamed').columns, inplace=True)

    return df

def filter_data(df, start_date=date_tool.get_first_day_of_previous_month(),
                end_date=date_tool.get_last_day_of_previous_month()):
    filtered_df = df[(df['并购重组年月'] >= start_date) & (df['并购重组年月'] <= end_date)]
    return filtered_df

def save_data(df_data, table_name):
    with open_mysql() as engine:
        load_to_MySQL_on_Cloud(df_data, engine, table_name)

def upload():
    c = Config()

    file_path = c.file_path
    sheet_name_acquisition = c.sheet_name_acquisition

    df = get_data(file_path, sheet_name_acquisition)

    filtered_df = filter_data(df)

    print(filtered_df)

    save_data(filtered_df, c.table_acquisition)
